<cfcomponent extends="base">

<!---***************************************************************************************************--->
<!---***************************************INSERTS*****************************************************--->
	
    <!---INSERTS a roster row for a clinic and for today's date.--->
    <cffunction name="FuncQryInsertRoster" returntype="any">
    	<cfargument required="yes" name="FORM" default="-1">
        
<!---queries table to find if a provider is already in database for new APT date.  If they are there, then don't insert again.--->
        <cfset find_provider = FuncQrySelectRoster(
			ARGUMENTS.FORM.Clinic, 
			ARGUMENTS.FORM.RosterDate, 
			ARGUMENTS.FORM.ProviderID
		)/>
        
        <cfif find_provider.recordCount lt 1 >
            <cfquery datasource="Intranet" result="qryInsertRoster">
                INSERT INTO [APTDailyRoster]
                    (
                    [ProviderID], 
                    [ResourceCode], 
                    [Title], 
                    [Shift], 
                    [CaseManagerID], 
                    [CMSSupportID], 
                    [CMAID], 
                    [Notes], 
                    [Clinic],
                    [RosterDate]
                    )
                VALUES
                    (<cfqueryparam value="#ARGUMENTS.FORM.ProviderID#" cfsqltype="cf_sql_integer" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.ResourceCode#" cfsqltype="cf_sql_varchar" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.Title#" cfsqltype="cf_sql_varchar" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.Shift#" cfsqltype="cf_sql_varchar" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.CaseManagerID#" cfsqltype="cf_sql_integer" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.CMSSupportID#" cfsqltype="cf_sql_integer" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.CMAID#" cfsqltype="cf_sql_integer" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.Notes#" cfsqltype="cf_sql_varchar" />, 
                     <cfqueryparam value="#ARGUMENTS.FORM.Clinic#" cfsqltype="cf_sql_varchar" />,
                     <cfqueryparam value="#ARGUMENTS.FORM.RosterDate#" cfsqltype="cf_sql_date" />
                     )
            </cfquery>
       </cfif>
    </cffunction>
    
    
    
    <!---INSERTS speical instructions for a clinic for today--->
    <cffunction name="FuncQryInsertSpecialInstructions" access="public" returntype="any" >
    	<cfargument required="yes" name="FORM" default="-1">
        
		<cfquery datasource="Intranet" result="qryInsertSpecialInstructions">
			INSERT INTO [APTSpecialInstructions]
				([Clinic], [RosterDate], [SpecialInstructions])
			VALUES
				(<cfqueryparam value="#ARGUMENTS.FORM.Clinic#" cfsqltype="cf_sql_varchar" />,
                 <cfqueryparam value="#ARGUMENTS.FORM.RosterDate#" cfsqltype="cf_sql_date" />,
                 <cfqueryparam value="#LTrim(ARGUMENTS.FORM.SpecialInstructions)#" cfsqltype="cf_sql_varchar" />
                 )
		</cfquery>	
    </cffunction>

    
    
    
    <!--- Allows admin to add "staff" that are not in the SCF system to the database --->
    <cffunction name="FuncQryAddToRoster" access="public" returntype="any" >
    	<cfargument required="yes" name="FORM" default="-1">
        
		<cfquery datasource="Intranet" result="newRosterAdd">
			INSERT INTO [APTAddedStaff]
				([FirstName], [LastName], [Clinic], [PhoneNumber], [SupportType])
			VALUES
				(<cfqueryparam value="#ARGUMENTS.FORM.FirstName#" cfsqltype="cf_sql_varchar" />,
                 <cfqueryparam value="#ARGUMENTS.FORM.LastName#" cfsqltype="cf_sql_varchar" />,
                 <cfqueryparam value="#ARGUMENTS.FORM.Clinic#" cfsqltype="cf_sql_varchar" />, 
                 <cfqueryparam value="#ARGUMENTS.FORM.Phone#" cfsqltype="cf_sql_varchar" />,
                 <cfqueryparam value="#ARGUMENTS.FORM.SupportType#" cfsqltype="cf_sql_varchar" />
                 )
		</cfquery>	
    </cffunction>
    
    
<!---*************************************INSERTS*********************************************************--->
<!---*****************************************************************************************************--->



<!---***************************************************************************************************--->
<!---***************************************SELECTS*****************************************************--->
	
    <!---SELECT APT roster by argument provided.--->
    <cffunction name="FuncQrySelectRoster" returntype="any">
    	<cfargument required="yes" name="Clinic" default="_AKA-PC 1 East">
        <cfargument required="yes" name="RosterDate" default="01/01/1900">
        <cfargument required="yes" name="ProviderID" default="-1">
        
		<cfquery datasource="Intranet" name="qrySelectRoster">
			SELECT
            	[AptID],
                [ProviderID], 
                [ResourceCode], 
                [Title], 
                [Shift], 
                [CaseManagerID], 
                [CMSSupportID], 
                [CMAID], 
                [Notes], 
                [Clinic],
                [RosterDate]
			FROM [APTDailyRoster]
            WHERE Clinic = <cfqueryparam value="#ARGUMENTS.Clinic#" cfsqltype="cf_sql_varchar" /> AND
            	RosterDate = <cfqueryparam value="#ARGUMENTS.RosterDate#" cfsqltype="cf_sql_date" />
                <cfif ARGUMENTS.ProviderID neq -1>
                	AND ProviderID = <cfqueryparam value="#ARGUMENTS.ProviderID#" cfsqltype="cf_sql_integer" />
                </cfif>
            ORDER BY RosterDate            
		</cfquery> 
        <cfreturn qrySelectRoster />
    </cffunction>
    
    
    
    <!---SELECTS speical instructions for a clinic for today--->
    <cffunction name="FuncQrySelectSpecialInstructions" access="public" returntype="any" >
    	<cfargument required="yes" name="Clinic" default="_AKA-PC 1 East">
        <cfargument required="yes" name="RosterDate" default="01/01/1900">

        <cfquery datasource="Intranet" name="qrySelectSpecialInstructions">
			SELECT [Clinic], [RosterDate], [SpecialInstructions]
            FROM [APTSpecialInstructions]
            WHERE SpecialInstructionsID = ( 	SELECT MAX(SpecialInstructionsID) as last_id
           					FROM [APTSpecialInstructions]
            				WHERE Clinic = <cfqueryparam value="#ARGUMENTS.Clinic#" cfsqltype="cf_sql_varchar" /> AND
            					RosterDate = <cfqueryparam value="#ARGUMENTS.RosterDate#" cfsqltype="cf_sql_date" /> )
		</cfquery>
        <cfreturn qrySelectSpecialInstructions />
    </cffunction>
    
    
    
    <!---Gets "staff" not in SCF system from data base to populate the drop down lists--->
    <cffunction name="FuncQryGetRoster" access="public" returntype="query" >
    	<cfargument required="no" name="SupportType" default="-1">
        
		<cfquery datasource="Intranet" name="qryGetRoster">
			SELECT [AddedID], [FirstName], [LastName], [Clinic], [PhoneNumber], [SupportType]
            FROM [APTAddedStaff]
            <cfif ARGUMENTS.SupportType neq "-1">
            	<cfif ARGUMENTS.SupportType eq "CMA/LPN" >
            		WHERE [SupportType] = <cfqueryparam value="CMA-LPN" cfsqltype="cf_sql_varchar" />
                <cfelseif ARGUMENTS.SupportType eq "CMA-LPN">
                	WHERE [SupportType] = <cfqueryparam value="CMA-LPN" cfsqltype="cf_sql_varchar" />
                <cfelse>
                	WHERE [SupportType] = <cfqueryparam value="#ARGUMENTS.SupportType#" cfsqltype="cf_sql_varchar" />
                </cfif>
            </cfif>
		</cfquery>
        <cfreturn qryGetRoster />	
    </cffunction>
    
    
    
    <!---Gets added staff by ID--->
    <cffunction name="FuncQryGetAddedStaffByID" access="public" returntype="any" >
    	<cfargument required="no" name="AddedID" default="-1">
		<cfquery datasource="Intranet" name="qryGetAddedStaffByID">
			SELECT [AddedID], [FirstName], [LastName], [Clinic], [PhoneNumber], [SupportType]
            FROM [APTAddedStaff]
            WHERE AddedID = <cfqueryparam value="#ARGUMENTS.AddedID#" cfsqltype="cf_sql_integer" />
		</cfquery>
        <cfif qryGetAddedStaffByID.RecordCount gt 0>
        	<cfset full_name = qryGetAddedStaffByID.LastName & ", " & qryGetAddedStaffByID.FirstName />
            <cfreturn full_name />
        <cfelse>
        	<cfreturn "" />
        </cfif>
        
    </cffunction>
    
    
<!---*************************************SELECTS*********************************************************--->
<!---*****************************************************************************************************--->



<!---***************************************************************************************************--->
<!---***************************************DELETES*****************************************************--->
	
    <!---Deletes a roster row by id--->
    <cffunction name="FuncQryDeleteRosterByID" returntype="any">
    	<cfargument required="yes" name="AptID" default="-1">

		<cfquery datasource="Intranet" result="qryDeleteRosterByID">
			DELETE
            FROM [APTDailyRoster]
            WHERE AptID = <cfqueryparam value="#ARGUMENTS.AptID#" cfsqltype="cf_sql_integer" />
		</cfquery>
    </cffunction>
    
    
    
    <!---Delete's a selected "staff" not in SCF system from database--->
    <cffunction name="FuncQryDeleteRoster" access="public" >
    	<cfargument required="yes" name="AddedID" default="-1">
        
		<cfquery datasource="Intranet" name="qryDeleteRoster">
			DELETE
            FROM [APTAddedStaff]
            WHERE [AddedID] = <cfqueryparam value="#ARGUMENTS.AddedID#" cfsqltype="cf_sql_integer" />
		</cfquery>
    </cffunction>
    
    
    
<!---*************************************DELETES*********************************************************--->
<!---*****************************************************************************************************--->
    
    
</cfcomponent>